package linuxbox.status.bean;

import java.util.*;
import java.sql.Timestamp;

import linuxbox.BoxStatic;
import linuxbox.exception.*;
import linuxbox.bean.*;
import linuxbox.util.*;
import linuxbox.status.logic.*;

public class StatusBean extends StatusObject implements ITableBean {
    protected DbAdapter m_db = null;
    protected static String m_TableName = "userstatus";
  
    public StatusBean(StatusKey pk, DbAdapter db) {
        this.init(pk, db);
    }

    public StatusBean(StatusKey pk) {
        this.init(pk, null);
    }

    public StatusBean() {
        this.init(null, null);
    }

    public void init(StatusKey pk) {
        init(pk, null);
    }

    public void init(StatusKey pk, DbAdapter db) {
        if (db != null) {
            m_db = db;
        }
        
        if (pk != null)
            m_iID = pk.getID();
    }

    public String getTableName() {
        return m_TableName;
    }
    
    public void setTableName(String str) {
        m_TableName = str;
    }

    public void makeConnection() throws LinuxboxException {
        try {
            if (m_db == null || !m_db.isGood()) m_db = new DbAdapter(true);
            //m_db = new DbAdapter();
        } catch (DbBeanException e) {
            e.printStackTrace();
            throw LinuxboxException.DBADAPTER_ERROR;
        }
    }
    
    public void closeConnection() throws LinuxboxException {
        try {
            if (m_db != null) m_db.close();
        } catch (DbBeanException e) {
            throw LinuxboxException.DBADAPTER_ERROR;
        }
    }

    public void load() throws LinuxboxException {
        try {
            String sql;
    
            sql = "select s.id, s.userid, s.loginid, s.firstname, s.lastname, s.dept_id, "
                + "d.name, s.checkin, s.checkout, s.status from " + m_TableName
                + " s, department d where s.id = " + m_iID + " and s.dept_id=d.id";
        
            BoxStatic.logger.debug(sql);
            makeConnection();
            m_db.executeQuery(sql);
    
            int i = 1;
            if (m_db.next()) {
                m_iID = m_db.getInt(i++);
                m_iUserID = m_db.getInt(i++);
                m_sLoginID = m_db.getString(i++);
                m_sFirstName = m_db.getString(i++);
                m_sLastName = m_db.getString(i++);
                m_iDeptID = m_db.getInt(i++);
                m_sDeptName = m_db.getString(i++);
                m_tsCheckIn = m_db.getTimestamp(i++);
                m_tsCheckOut = m_db.getTimestamp(i++);
                m_iStatus = m_db.getInt(i++);
            } else {
                closeConnection();
                throw new LinuxboxException(ExceptionHelper.STATUS, "U000001");
            }
            
            closeConnection();
        } catch (DbBeanException e) {
            throw LinuxboxException.DBADAPTER_ERROR;
        }
    }

    public void store() throws LinuxboxException {
        String checkin = BoxStatic.formater.postgresTimestamp(m_tsCheckIn);
        String checkout = BoxStatic.formater.postgresTimestamp(m_tsCheckOut);
                
        String sql = "UPDATE " + m_TableName + " set"
            + " id = " + m_iID + ","
            + " userid = " + m_iUserID + ","
            + " loginid = '" + m_sLoginID + "',"
            + " firstname = '" + m_sFirstName + "',"
            + " lastname = '" + m_sLastName + "',"
            + " dept_id = " + m_iDeptID + ","
            + " checkin = '" + checkin + "',"
            + " checkout = '" + checkout + "',"
            + " status = " + m_iStatus
            + " where id = " + m_iID;
                     
        makeConnection();

        try {
            m_db.executeUpdate(sql);
        } catch (DbBeanException e) {
            throw LinuxboxException.DBADAPTER_ERROR;
        }

        closeConnection();
    }

    public void updateCheckIn(Timestamp in) throws LinuxboxException {
        String checkin = BoxStatic.formater.postgresTimestamp(in);
                
        String sql = "UPDATE " + m_TableName + " set checkin = '" 
            + checkin + "', status = " + StatusManager.ONLINE
            + " where id = " + m_iID;
                     
        makeConnection();

        try {
            m_db.executeUpdate(sql);
        } catch (DbBeanException e) {
            throw LinuxboxException.DBADAPTER_ERROR;
        }
        
        closeConnection();
    }

    public void updateCheckOut(Timestamp out) throws LinuxboxException {
        String checkout = BoxStatic.formater.postgresTimestamp(out);
                
        String sql = "UPDATE " + m_TableName + " set checkout = '" 
            + checkout + "', status = " + StatusManager.CHECKOUT 
            + " where id = " + m_iID;
                     
        makeConnection();

        try {
            m_db.executeUpdate(sql);
        } catch (DbBeanException e) {
            throw LinuxboxException.DBADAPTER_ERROR;
        }

        closeConnection();
    }

    public void updateUserStatus(int status) throws LinuxboxException {
        String sql = "UPDATE " + m_TableName + " set status = " 
        + status + " where id = " + m_iID;
                     
        makeConnection();

        try {
            m_db.executeUpdate(sql);
        } catch (DbBeanException e) {
            throw LinuxboxException.DBADAPTER_ERROR;
        }

        closeConnection();
    }

    public void refresh() throws LinuxboxException {
        load();
    }

    public void create() throws LinuxboxException {
		if (m_iID == -1) m_iID = nextID();
        String checkin = BoxStatic.formater.postgresTimestamp(m_tsCheckIn);
        String checkout = BoxStatic.formater.postgresTimestamp(m_tsCheckOut);
		        
        String sql = "insert into " + m_TableName + "(id, userid, loginid, firstname, lastname, "
            + "dept_id, checkin, checkout, status) values(" 
            + m_iID + ", " 
            + m_iUserID + ", '" 
            + m_sLoginID + "', '"
            + m_sFirstName + "', '"
            + m_sLastName + "', "
            + m_iDeptID + ", '"
            + checkin + "', '" + checkout + "', "
            + m_iStatus + ")";

        BoxStatic.logger.debug(sql);

        makeConnection();
        try {
            m_db.executeUpdate(sql);
        } catch (DbBeanException e) {
            e.printStackTrace();
            closeConnection();
            throw LinuxboxException.DBADAPTER_ERROR;
        }
        closeConnection();
    }

    public void create(StatusObject obj) throws LinuxboxException {
	    BoxStatic.logger.debug("I am in StatusBean.create(StatusObject) now ... ");
        copyFrom(obj);
        create();
    }

    public void create(int userID) throws LinuxboxException {
	    BoxStatic.logger.debug("I am in StatusBean.create(int) now ... ");

        String sql = null;
        
        // id must have value, cause id in Status table should be the 
        // same as id in UserControl table.
		m_iID = nextID();
		        
        try {
            sql = "select id, loginid, firstname, lastname, dept_id "
                + "from usercontrol where id = " + userID;
            BoxStatic.logger.debug(sql);
            makeConnection();
            if (m_db == null) BoxStatic.logger.debug("m_db is null");
            m_db.executeQuery(sql);
            
            int i = 1;
            if (m_db.next()) {
                m_iUserID = m_db.getInt(i++);         
                m_sLoginID = m_db.getString(i++);
                m_sFirstName = m_db.getString(i++);
                m_sLastName = m_db.getString(i++);
                m_iDeptID = m_db.getInt(i++);
        
                sql = "insert into " + m_TableName + "(id, userid, loginid, "
                    + "firstname, lastname, dept_id, status) values(" 
                    + m_iID + ", " 
                    + m_iUserID + ", '" 
                    + m_sLoginID + "', '"
                    + m_sFirstName + "', '"
                    + m_sLastName + "', "
                    + m_iDeptID + ", "
                    + StatusManager.ONLINE + ")";
        
                BoxStatic.logger.debug(sql);
    
                m_db.executeUpdate(sql);
            }
        } catch (DbBeanException e) {
            closeConnection();
            throw LinuxboxException.DBADAPTER_ERROR;
        }

        closeConnection();
    }

    protected int nextID() throws LinuxboxException {
        makeConnection();
        int id = -1;
		String sql = "select max(id) from " + m_TableName;
        try {
			m_db.executeQuery(sql);

			if (m_db.next()) {
				id = m_db.getInt(1) + 1;
			}
        } catch (DbBeanException e) {
            closeConnection();
            throw LinuxboxException.DBADAPTER_ERROR;
        }
        closeConnection();
        return id;
    }
    
    public void remove() throws LinuxboxException {
        String sql = "delete from " + m_TableName + " where id = " + m_iID;
        BoxStatic.logger.debug(sql);
        makeConnection();

        try {
            m_db.executeUpdate(sql);
        } catch (DbBeanException e) {
            throw LinuxboxException.DBADAPTER_ERROR;
        }

        closeConnection();
    }

    public void moveStatusToHistory() throws LinuxboxException {
        setTableName("userstatus");
        load();
        int preID = m_iID;
        setTableName("userstatus_history");
        m_iID = -1;
        create();
        setTableName("userstatus");
        m_iID = preID;
        remove();
    }

    public Object findByKey() throws LinuxboxException {
        String sql = "select id from " + m_TableName + " where id = " + m_iID;
        makeConnection();
        StatusKey pk = null;

        try {
            m_db.executeQuery(sql);

            if(m_db.next()) {
                pk = new StatusKey(m_db.getInt(1));
            }
        } catch (DbBeanException e) {
            throw LinuxboxException.DBADAPTER_ERROR;
        }

        closeConnection();
        return pk;
    }

    public Iterator findBySql(String sqlWhere) throws LinuxboxException {
        List v = new ArrayList();
        StatusKey pk = null;
    
        String sql = "select id from " + m_TableName + sqlWhere;
        BoxStatic.logger.debug(sql);
        makeConnection();

        try {
            m_db.executeQuery(sql);

            while (m_db.next()) {
                pk = new StatusKey(m_db.getInt(1));
                v.add(pk);
            }
        } catch (DbBeanException e) {
            throw LinuxboxException.DBADAPTER_ERROR;
        }

        closeConnection();

        return v.iterator();
    }

    public StatusKey findByUserID(int userID) throws LinuxboxException {
        StatusKey pk = null;
    
        String sql = "select id from " + m_TableName + " where userid = " + userID;
        BoxStatic.logger.debug(sql);
        makeConnection();

        try {
            m_db.executeQuery(sql);

            if (m_db.next()) {
                pk = new StatusKey(m_db.getInt(1));
            }
        } catch (DbBeanException e) {
            throw LinuxboxException.DBADAPTER_ERROR;
        }

        closeConnection();

        return pk;
    }
}
